﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;


namespace ProjectTerra
{
    
    static class DatabaseHandler
    {
        private static MySqlConnection mySqlCon;
        private static MySqlCommand command;
        private static MySqlDataReader reader;

        /*
         * Simple query function for testing
         */
        #region Methods

        static void query(string queryString)
        {
        }

        public static Car getCarbyMakeModel(string make, string model)
        {
            Car retrievedCar = new Car();

            command = mySqlCon.CreateCommand();

            if (make.Length != 0 && model.Length != 0)
            {
                command.CommandText = "Select * From Vehicles where " +
                    "Make = " + make + "AND model = " + model;
            }
                try{
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        retrievedCar.Year = reader.GetInt32("V_Year");
                        retrievedCar.Make = reader.GetString("Make");
                        retrievedCar.Model = reader.GetString("Model");
                        retrievedCar.VIN_Number = reader.GetInt32("VIN");
                        retrievedCar.exColor = reader.GetString("Exterior_Color");
                        retrievedCar.inColor = reader.GetString("Interior_Color");
                        retrievedCar.Condition = reader.GetString("V_Condition");
                        retrievedCar.Mileage = reader.GetInt32("Mileage");
                        retrievedCar.TagPrice = reader.GetFloat("MSRP");
                        retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                        retrievedCar.EngineType = reader.GetString("V_Engine");
                        retrievedCar.fuelType = reader.GetString("Fuel_Type");
                        retrievedCar.driveType = reader.GetString("Drive_Type");
                        retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                        retrievedCar.Torque = reader.GetInt32("Torque");
                        retrievedCar.Transmission = reader.GetString("Transmission");
                        retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                        retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                        retrievedCar.Features = reader.GetString("Features");
                        retrievedCar.Warranty = reader.GetString("Warranty");
                        retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                    }
                    reader.Close();
                }
                catch (Exception ex) { }
                return retrievedCar;
        }

        public static Car getCarByYear(int min, int max)
        {
            Car retrievedCar = new Car();

            if (max < min)
                throw new System.ArgumentException("Highest year cannot be lower than lowest year", "original");


            command = mySqlCon.CreateCommand();

            if(min.ToString().Length  == 0)
                command.CommandText = "Select * from Vehicles Where " +
                    "Year < " + max;
            else if(max.ToString().Length == 0)
                command.CommandText = "Select * from Vehicles Where " +
                    "Year > " + min;
            else
                command.CommandText = "Select * from Vehicles Where " +
                    "Year > " + min + "and Year < " + max;

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    retrievedCar.Year = reader.GetInt32("V_Year");
                    retrievedCar.Make = reader.GetString("Make");
                    retrievedCar.Model = reader.GetString("Model");
                    retrievedCar.VIN_Number = reader.GetInt32("VIN");
                    retrievedCar.exColor = reader.GetString("Exterior_Color");
                    retrievedCar.inColor = reader.GetString("Interior_Color");
                    retrievedCar.Condition = reader.GetString("V_Condition");
                    retrievedCar.Mileage = reader.GetInt32("Mileage");
                    retrievedCar.TagPrice = reader.GetFloat("MSRP");
                    retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                    retrievedCar.EngineType = reader.GetString("V_Engine");
                    retrievedCar.fuelType = reader.GetString("Fuel_Type");
                    retrievedCar.driveType = reader.GetString("Drive_Type");
                    retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                    retrievedCar.Torque = reader.GetInt32("Torque");
                    retrievedCar.Transmission = reader.GetString("Transmission");
                    retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                    retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                    retrievedCar.Features = reader.GetString("Features");
                    retrievedCar.Warranty = reader.GetString("Warranty");
                    retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                }
                reader.Close();
            }
            catch (Exception ex) { }

            return retrievedCar;
        }

        public static Car getCarbyMileage(int min, int max)
        {
            Car retrievedCar = new Car();
            if (max < min)
                throw new System.ArgumentException("Max mileage cannot be lower than minimum mileage", "original");

            command = mySqlCon.CreateCommand();

            if (min.ToString().Length == 0)
                command.CommandText = "Select * From Vehicles Where " +
                    "Mileage < " + max;
            else if (max.ToString().Length == 0)
                command.CommandText = "Select * From Vehicles Where " +
                    "Mileage > " + min;
            else
                command.CommandText = "Select * From Vehicles Where " +
                    "Mileage > " + min + "AND Mileage < " + max;

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    retrievedCar.Year = reader.GetInt32("V_Year");
                    retrievedCar.Make = reader.GetString("Make");
                    retrievedCar.Model = reader.GetString("Model");
                    retrievedCar.VIN_Number = reader.GetInt32("VIN");
                    retrievedCar.exColor = reader.GetString("Exterior_Color");
                    retrievedCar.inColor = reader.GetString("Interior_Color");
                    retrievedCar.Condition = reader.GetString("V_Condition");
                    retrievedCar.Mileage = reader.GetInt32("Mileage");
                    retrievedCar.TagPrice = reader.GetFloat("MSRP");
                    retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                    retrievedCar.EngineType = reader.GetString("V_Engine");
                    retrievedCar.fuelType = reader.GetString("Fuel_Type");
                    retrievedCar.driveType = reader.GetString("Drive_Type");
                    retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                    retrievedCar.Torque = reader.GetInt32("Torque");
                    retrievedCar.Transmission = reader.GetString("Transmission");
                    retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                    retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                    retrievedCar.Features = reader.GetString("Features");
                    retrievedCar.Warranty = reader.GetString("Warranty");
                    retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                }
                reader.Close();
            }
            catch (Exception ex) { }

            return retrievedCar;
        }

        public static Car getCarByPrice(string type, int min, int max)
        {
            Car retrievedCar = new Car();
            
            if (max < min)
                throw new System.ArgumentException("Max price cannot be lower than minimum price", "original");

            command = mySqlCon.CreateCommand();

            if (type.Equals("msrp"))
            {
                if (min.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "MSRP < " + max;
                else if (max.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "MSRP > " + min;
                else
                    command.CommandText = "Select * From Vehicles where " +
                        "MSRP > " + min + "AND MSRP < " + max;
            }

            if(type.Equals("sale"))
            {
                if (min.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "Min_Sale_Price < " + max;
                else if (max.ToString().Length == 0)
                    command.CommandText = "Select * From Vehicles where " +
                        "Min_Sale_Price > " + min;
                else
                    command.CommandText = "Select * From Vehicles where " +
                        "Min_Sale_Price > " + min + "AND Min_Sale_Price < " + max;
            }

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    retrievedCar.Year = reader.GetInt32("V_Year");
                    retrievedCar.Make = reader.GetString("Make");
                    retrievedCar.Model = reader.GetString("Model");
                    retrievedCar.VIN_Number = reader.GetInt32("VIN");
                    retrievedCar.exColor = reader.GetString("Exterior_Color");
                    retrievedCar.inColor = reader.GetString("Interior_Color");
                    retrievedCar.Condition = reader.GetString("V_Condition");
                    retrievedCar.Mileage = reader.GetInt32("Mileage");
                    retrievedCar.TagPrice = reader.GetFloat("MSRP");
                    retrievedCar.LowestSalePrice = reader.GetFloat("Min_Sale_Price");
                    retrievedCar.EngineType = reader.GetString("V_Engine");
                    retrievedCar.fuelType = reader.GetString("Fuel_Type");
                    retrievedCar.driveType = reader.GetString("Drive_Type");
                    retrievedCar.Horsepower = reader.GetInt32("Horsepower");
                    retrievedCar.Torque = reader.GetInt32("Torque");
                    retrievedCar.Transmission = reader.GetString("Transmission");
                    retrievedCar.CityFE = reader.GetInt32("Fuel_Economy_City");
                    retrievedCar.HighwayFE = reader.GetInt32("Fuel_Economy_Highway");
                    retrievedCar.Features = reader.GetString("Features");
                    retrievedCar.Warranty = reader.GetString("Warranty");
                    retrievedCar.DateReceived = reader.GetDateTime("Date_Received");
                }
                reader.Close();
            }
            catch (Exception ex) { }
            return retrievedCar;
        }

        public static void updateCar(Car car) 
        {

            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Vehicles " +
                "Set V_Year = " + car.Year + ", " +
                "Make = '" + car.Make + "', " +
                "Model = '" + car.Model + "', " +
                "VIN = " + car.VIN_Number + ", " +
                "Type_ID = '" + car.VehicleType + "', " +
                "Exterior_Color = '" + car.exColor + "', " +
                "Interior_Color = '" + car.inColor + "', " +
                "V_Condition = '" + car.Condition + "', " +
                "Mileage = " + car.Mileage + ", " +
                "MSRP = " + car.TagPrice + ", " +
                "Min_Sale_Price = " + car.LowestSalePrice + ", " +
                "V_Engine = '" + car.EngineType + "', " +
                "Fuel_Type = '" + car.fuelType + "', " +
                "Drive_Type = '" + car.driveType + "', " +
                "Horsepower = " + car.Horsepower + ", " +
                "Torque = " + car.Torque + ", " +
                "Transmission = '" + car.Transmission + "', " +
                "Fuel_Economy_City = " + car.CityFE + ", " +
                "Fuel_Economy_Highway = " + car.HighwayFE + ", " +
                "Features = '" + car.Features + "', " +
                "Warranty = '" + car.Warranty + "', " +
                "Date_Received = '" + car.DateReceived + "', " +
                "S_ID = " + car.Status + ", " +
                "Where VIN = " + car.VIN_Number;

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }

        public static void addCar(Car newCar)
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "INSERT INTO Vehicles(V_Year, Make, Model, VIN, Type_ID, Exterior_Color, Interior_Color, V_Condition, Mileage, MSRP,"+
            "Min_Sale_Price, V_Engine, Fuel_Type, Drive_Type, Horsepower, Torque, Transmission, Fuel_Economy_City, Fuel_Economy_Highway," +
            "Features, Warranty, Date_Received, S_ID) " +
                "Values(" + newCar.Year +
                ", " + newCar.Make + ", " + newCar.Model +
                ", " + newCar.VIN_Number + ", " + newCar.VehicleType +
                ", " + newCar.exColor + ", " + newCar.inColor +
                ", " + newCar.Condition + ", " + newCar.Mileage +
                ", " + newCar.TagPrice + ", " + newCar.LowestSalePrice +
                ", " + newCar.EngineType +  ", " + newCar.fuelType +
                ", " + newCar.driveType + ", " + newCar.Horsepower + 
                ", " + newCar.Torque + ", " + newCar.Transmission + 
                ", " + newCar.CityFE + ", " + newCar.HighwayFE + 
                ", " + newCar.Features + ", " + newCar.Warranty +
                ", " + newCar.DateReceived + ", " + newCar.Status + ")";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public static void removeCar(Car removeCar)
        {

            command = mySqlCon.CreateCommand();

            command.CommandText = "Delete FROM Vehicles " +
                "WHERE VIN = " + removeCar.VIN_Number + ")";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public static Customer getCustomer(Customer Customer) 
        {
            Customer retrivedCustomer = new Customer();

            command = mySqlCon.CreateCommand();

            command.CommandText = "Select * From Customers where " +
                    "Last_Name LIKE  '%" + Customer.Info.LastName + "%' OR " +
                    "Middle_Name LIKE '%" + Customer.Info.MiddleName + "%' OR " +
                    "First_Name = '%" + Customer.Info.FirstName + "%' OR " +
                    "Home_Phone = '" + Customer.Info.Homephone + "' OR " +
                    "Cell_Phone = '" + Customer.Info.Cell + "' OR "+
                    "Work_Phone = '" + Customer.Info.Workphone + "' OR " +
                    "Address LIKE '%" + Customer.Info.Address + "%' OR " +
                    "Interested_in LIKE '%" + Customer.Interested + "%' OR " +
                    "Additional_notes LIKE '%" + Customer.Interested + "%' OR ";

            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    retrivedCustomer.Customer_ID = reader.GetInt32("Customer_ID");
                    retrivedCustomer.Info.FirstName = reader.GetString("First_Name");
                    retrivedCustomer.Info.MiddleName = reader.GetString("Middle_Name");
                    retrivedCustomer.Info.LastName = reader.GetString("Last_Name");
                    retrivedCustomer.Info.Homephone = reader.GetString("Home_Phone");
                    retrivedCustomer.Info.Cell = reader.GetString("Cell_Phone");
                    retrivedCustomer.Info.Workphone = reader.GetString("Work_Phone");
                    retrivedCustomer.Info.Address = reader.GetString("Address");
                    retrivedCustomer.Interested = reader.GetString("Interested_in");
                    retrivedCustomer.AdditionalInfo = reader.GetString("Additional_notes");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }
            return retrivedCustomer;
        }

        public static void updateCustomer(Customer customer) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Customers " +
                "Set Last_Name = " + customer.Info.LastName + ", " +
                "Middle_Name = " + customer.Info.MiddleName + ", " +
                "First_Name = " + customer.Info.FirstName + ", " +
                "Home_Phone = " + customer.Info.Homephone + ", " +
                "Cell_Phone = " + customer.Info.Cell + ", " +
                "Work_Phone = " + customer.Info.Workphone + ", " +
                "Address = " + customer.Info.Address + ", " +
                "Interested_In = " + customer.Interested + ", " +
                "Additional_Notes = " + customer.AdditionalInfo + " " +
                "Where Customer_ID = " + customer.Customer_ID;

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }

        public static void addCustomer(Customer newCustomer)
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "insert into Customers(Last_Name, Middle_Name, First_Name, Home_Phone, " +
            "Cell_Phone, Work_Phone, Address, Interested_in, Additional_notes)" +
                "Values('" + newCustomer.Info.LastName +
                "', '" + newCustomer.Info.MiddleName +
                "', '" + newCustomer.Info.FirstName +
                "', '" + newCustomer.Info.Homephone +
                "', '" + newCustomer.Info.Cell +
                "', '" + newCustomer.Info.Workphone +
                "', '" + newCustomer.Info.Address +
                "', '" + newCustomer.Interested +
                "', '" + newCustomer.AdditionalInfo + "')";
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public static void removeCustomer(Customer removedCustomer)
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Delete from Customers " + 
                    "Where Customer_ID = " + removedCustomer.Customer_ID + " and " + 
                    "Last_Name ='" + removedCustomer.Info.LastName + 
                    "' AND First_Name = '" + removedCustomer.Info.FirstName + "'";
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public static Dealership getDealership(string name)
        {
            Dealership newDealership = new Dealership();

            command = mySqlCon.CreateCommand();

            command.CommandText = "select * from Dealership where Name LIKE '%"
                + name + "%'";
            try
            {
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    newDealership.Dealership_ID = reader.GetInt32("Dealership_ID");
                    newDealership.ContactInformation.FirstName = reader.GetString("Name");
                    newDealership.ContactInformation.Address = reader.GetString("Address");
                    newDealership.Owner = reader.GetString("Owner");
                    newDealership.ContactInformation.Workphone = reader.GetString("Phone");
                    newDealership.Services = reader.GetString("Services");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
            }

            return newDealership;
        }

        public static void updateDealership(Dealership dealership) {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Dealerships " +
                "Set Ownership_ID = " + dealership.Dealership_ID + ", " +
                "Name = '" + dealership.ContactInformation.FirstName+ "', " +
                "Address = '" + dealership.ContactInformation.Address + "', " +
                "Owner = " + dealership.Owner + ", " +
                "Phone = '" + dealership.ContactInformation.Workphone + "', " +
                "Services = '" + dealership.Services + "', " +
                "Where Ownership_ID = " + dealership.Dealership_ID;
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }


        public static void addDealership(Dealership newPlace) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Insert into Dealerships(Name, Address, Owner, Phone, Service) " +
                "Values('" + newPlace.ContactInformation.FirstName +
                "', '" + newPlace.ContactInformation.Address +
                "', '" + newPlace.Owner +
                "', " + newPlace.ContactInformation.Workphone +
                ", '" + newPlace.Services + "')";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }

        public static void removeDealership(Dealership oldPlace) 
        {

            command = mySqlCon.CreateCommand();

            command.CommandText = "Delete  from Dealerships " +
                "Where Dealership_ID = " + oldPlace.Dealership_ID;

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }



        public static void addEmployee(Employee employee) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "INSERT INTO Employees(Last_Name, Middle_Name, First_Name, Employee_ID, Home_Phone, " +
                "Work_Phone, Cell_Phone, Speciality)" +
                "Values(" + employee.Info.LastName +
                ", " + employee.Info.MiddleName + ", " + employee.Info.FirstName +
                ", " + employee.IDNumber + ", " + employee.Info.Homephone +
                ", " + employee.Info.Workphone + ", " + employee.Info.Cell +
                ", " + employee.Specialty + ")";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }


        public static void removeEmployee(Employee oldEmployee)
        {
           command.CommandText = "Delete FROM Employees " +
                "WHERE Employee_ID = " + oldEmployee.IDNumber + ")";

            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex)
            {
            }
        }



        public static void updateEmployee(Employee e) 
        {
            command = mySqlCon.CreateCommand();

            command.CommandText = "Update Employees " +
                "Set Last_Name = " + e.Info.LastName + ", " +
                "First_Name = '" + e.Info.FirstName + "', " +
                "Middle_Name = '" + e.Info.MiddleName + "', " +
                "Employee_ID = " + e.IDNumber + ", " +
                "Home_Phone = '" + e.Info.Homephone + "', " +
                "Work_Phone = '" + e.Info.Workphone + "', " +
                "Cell_Phone = '" + e.Info.Cell + "', " +
                "Speciality = '" + e.Specialty + "', " +
                "Where Employee_ID = " + e.IDNumber;
            try
            {
                reader = command.ExecuteReader();
                reader.Close();
            }
            catch (Exception ex) { }
        }




        public static void connect()
        {
            string MyConString = "SERVER=database2.cs.tamu.edu;" + "DATABASE=gul-TerraNet;" + "UID=gul;" + "PASSWORD=terranet431;";
            mySqlCon = new MySqlConnection(MyConString);
            try
            {
                mySqlCon.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        public static void disconnect()
        {
            if (reader.Read())
                reader.Close();
            mySqlCon.Close();
        }

        #endregion
    }
}
